package com.cn.utils;
import java.lang.reflect.*;
import java.sql.*;
import java.util.ArrayList;
import org.junit.Test;
import com.cn.domain.User;

public class BaseDao {  
    //查询所有  
    public ArrayList getList(Class cl){  //外键ID  --> 对象
		
        ArrayList list = new ArrayList(); 
        
        Connection conn = DBUtils.getConnByC3P0();  
       // System.out.println("conn="+conn);
        PreparedStatement ps = null;  
        ResultSet rs = null;  
        //由于数据库中的表名对应的是实体类的类名，所以可以通过传入的类得到表名cl.getSimpleName()  
        String sql = "select * from " + cl.getSimpleName();  
        //获取类对象的所有属性  
        Field[] fi = cl.getDeclaredFields();  
        try {  
            ps = conn.prepareStatement(sql);  
            rs = ps.executeQuery();
            while(rs.next()){  
                Object object = cl.newInstance();//实例化类对象  
                for(Field ff:fi){  
                    ff.setAccessible(true);//打开控制访问权限  
                    Object obj=ff.getName();
                    System.out.println(ff.getName());
                    ff.set(object, rs.getObject(ff.getName()));  
                }  
                list.add(object);  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
        	DBUtils.close(rs, ps, conn);  
        }  
        return list;  
    }  
  
    //根据表的主键查询表的对象  
    public Object getObjectById(Class cl,int id){  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        ResultSet rs = null;  
        Field[] fi = cl.getDeclaredFields();  
        //由于类中不一定用id表示编号，但是通常类中的第一个属性为编号id  
        String sql = "select * from " + cl.getSimpleName() + " where " + fi[0].getName() + " = " + id;  
        Object object = null;  
        try {  
            ps = conn.prepareStatement(sql);  
            rs = ps.executeQuery();  
            while(rs.next()){  
                object = cl.newInstance();  
                for(Field ff:fi){  
                    ff.setAccessible(true);  
                    ff.set(object, rs.getObject(ff.getName()));  
                }  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            DBUtils.close(rs, ps, conn); 
        }  
        return object;  
    }  
      
    //根据特定条件查询  
    public static ArrayList getListByCondition(Class cl,String name,Object value){  
        ArrayList list = new ArrayList();  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        ResultSet rs = null;  
          
        String sql = "select * from " + cl.getSimpleName() + " where " + name + " = '" + value+"'";
        
        Field[] fi = cl.getDeclaredFields();  
        try {  
            ps = conn.prepareStatement(sql);  
            rs = ps.executeQuery();  
            while(rs.next()){  
                Object object = cl.newInstance();//实例化类对象  
                for(Field ff:fi){  
                    ff.setAccessible(true);//打开控制访问权限  
                    ff.set(object, rs.getObject(ff.getName()));  
                }  
                list.add(object);  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            DBUtils.close(rs, ps, conn);  
        }  
        return list;  
    }  
    public static void main(String[] args) {  
        
        //测试getList()  
        BaseDao baseDao = new BaseDao();  
        baseDao.getList(User.class);
        System.out.println("00000000000000000");
        Connection conn = DBUtils.getConnByC3P0();  
    }  
      
    //插入对象  
    public boolean insert(Object object){  
        boolean flag = false;  
  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        //获取对象的类  
        Class cl = object.getClass();  
        Field[] fi = cl.getDeclaredFields();  
                //insert into Person(name) values(?,?,?)  
                //以下开始拼接sql语句  
                //两个String对象的连接是很耗费资源的，以下方法可以通过StringBuffer优化，  
                //可以减少资源利用，使用apand对StringBuffer进行拼接  
        String sql = "insert into " + cl.getSimpleName() + " (";  
        for (int i = 1; i < fi.length; i++) {  
            sql = sql + fi[i].getName();  
            if (i < fi.length-1) {  
                sql = sql + ",";  
            }  
        }  
        sql = sql + ") values(";  
        for (int i = 1; i < fi.length; i++) {  
            sql = sql + "?";  
            if (i < fi.length-1) {  
                sql = sql + ",";  
            }  
        }  
        sql = sql + ")";  
          
        try {  
            ps = conn.prepareStatement(sql);  
            for(int i = 1;i<fi.length;i++){  
                fi[i].setAccessible(true);  
                ps.setObject(i, fi[i].get(object));  
            }  
            int a = ps.executeUpdate();  
            if(a > 0){  
                flag = true;  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
            DBUtils.close(ps, conn);  
        }  
        return flag;  
  
    }  
  
    //优化插入  
    public boolean insert1(Object object){  
        boolean flag = false;  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        Class cl = object.getClass();  
        Field[] fi = cl.getDeclaredFields();  
        StringBuffer sb = new StringBuffer();  
        sb.append("insert into ");  
        sb.append(cl.getSimpleName());  
        sb.append(" (");  
        for(int i = 1;i<fi.length;i++){  
            sb.append(fi[i].getName());  
            if(i!=fi.length-1){  
                sb.append(" , ");  
            }  
        }  
        sb.append(") values (");  
        for(int i = 1;i<fi.length;i++){  
            sb.append(" ? ");  
            if(i!=fi.length-1){  
                sb.append(" , ");  
            }  
        }  
        sb.append(" ) ");  
        try {  
            ps = conn.prepareStatement(sb.toString());  
            for(int i = 1;i<fi.length;i++){  
                fi[i].setAccessible(true);  
                ps.setObject(i, fi[i].get(object));  
            }  
            int a = ps.executeUpdate();  
            if(a>0){  
                flag = true;  
            }  
              
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
        	DBUtils.close(ps, conn);
        }  
        return flag;  
    }  
      
    //更新  
    public boolean update(Object object){  
        boolean flag = false;  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        Class cl = object.getClass();  
        Field[] fi = cl.getDeclaredFields();  
        StringBuffer sb = new StringBuffer();  
            //update person set name = ?,age = ?,type = ? where id = ?  
        sb.append(" update ");  
        sb.append(cl.getSimpleName());  
        sb.append(" set ");  
        for(int i = 1;i<fi.length;i++){  
            fi[i].setAccessible(true);  
            sb.append(fi[i].getName());  
            sb.append(" = ? ");  
            if(i!=fi.length-1){  
                sb.append(" , ");  
            }  
        }  
        sb.append(" where ");  
        sb.append(fi[0].getName());  
        sb.append("=?");  
          
        try {  
            ps = conn.prepareStatement(sb.toString());  
            for(int i = 1;i<fi.length;i++){  
                fi[i].setAccessible(true);  
                ps.setObject(i, fi[i].get(object));  
            }  
            fi[0].setAccessible(true);  
            ps.setObject(fi.length, fi[0].get(object));  
            int a = ps.executeUpdate();  
            if(a>0){  
                flag = true;  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
        	DBUtils.close(ps, conn); 
        }  
        return flag;  
    }  
      
    //根据id删除  
    public boolean delete(Class cl , int id){  
        boolean flag = false;  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        Field[] fi = cl.getDeclaredFields();  
        String sql = "delete from "+cl.getSimpleName()+" where "+fi[0].getName()+" = ?";  
        try {  
            ps = conn.prepareStatement(sql);  
            ps.setObject(1, id);  
            int a = ps.executeUpdate();  
            if(a>0){  
                flag = true;  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
        	DBUtils.close(ps, conn);
        }  
        return flag ;  
      
    }  
      
    //根据特定条件删除  
    public boolean deleteByCondition(Class cl , String name,Object value){  
        boolean flag = false;  
        Connection conn = DBUtils.getConnByC3P0();  
        PreparedStatement ps = null;  
        Field[] fi = cl.getDeclaredFields();  
        String sql = "delete from "+cl.getSimpleName()+" where "+name+" = ?";  
        try {  
            ps = conn.prepareStatement(sql);  
            ps.setObject(1, value);  
            int a = ps.executeUpdate();  
            if(a>0){  
                flag = true;  
            }  
        } catch (Exception e) {  
            e.printStackTrace();  
        }finally{  
        	DBUtils.close(ps, conn);
        }  
        return flag ;  
      
    }  
  
      
   
}  